﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using DAL;
using System.Data;
using DevExpress.XtraCharts;
using DevExpress.Web.ASPxGridView;
using System.Text;
using System.Data.SqlClient;
using System.IO;
public partial class DwCount : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
             ChartDataBind(ViewType.Bar);
            //ShowTableData();
            //pieDatabind();
            data();


        }
    }
    //以表格方式显示

    void data()
    {
        string strSql = @"select cqmc,[01] as qyl ,[02] as stmfl,[03] as gyl,[04] as sqzzl
from(select dwxz,sscq from css_dwxx) as t PIVOT(count(t.dwxz) for t.dwxz in([01],[02],[03],[04])) as p,
css_cqxx as c where c.cqbh = p.sscq";
        DataTable dt = new DataTable();
        dt = Frame.Global.GlobalSqlHelper.ExecuteSQLToDataTable(strSql);

        gvShow.DataSource = dt;
        gvShow.DataBind();
    }
    //以图形方式显示
    void ChartDataBind(ViewType type)
    {
        DataTable dt = new DataTable();
        string strSql = @"select cqmc,[01] as qyl ,[02] as stmfl,[03] as gyl,[04] as sqzzl
        from(select dwxz,sscq from css_dwxx) as t PIVOT(count(t.dwxz) for t.dwxz in([01],[02],[03],[04])) as p,
        css_cqxx as c where c.cqbh = p.sscq";
        Series series_q = new Series("企业类", type);
        Series series_s = new Series("社团民非类", type);
        Series series_g = new Series("公益类", type);
        Series series_z = new Series("社区组织类", type);
        SeriesPoint point;
        dt = Frame.Global.GlobalSqlHelper.ExecuteSQLToDataTable(strSql);
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            point = new SeriesPoint(dt.Rows[i]["cqmc"].ToString(), dt.Rows[i]["qyl"].ToString());
            series_q.Points.Add(point);

            point = new SeriesPoint(dt.Rows[i]["cqmc"].ToString(), dt.Rows[i]["stmfl"].ToString());
            series_s.Points.Add(point);

            point = new SeriesPoint(dt.Rows[i]["cqmc"].ToString(), dt.Rows[i]["gyl"].ToString());
            series_g.Points.Add(point);

            point = new SeriesPoint(dt.Rows[i]["cqmc"].ToString(), dt.Rows[i]["sqzzl"].ToString());
            series_z.Points.Add(point);
        }
        #region
        //        #region 企业类
        //        DataTable dtCq = new DataTable();
        //        string strCq = @"select COUNT(a.dwxz) as 数量统计,b.cqmc from css_dwxx as a,css_cqxx as b 
        // where sscq in (select cqbh from css_cqxx) and dwxz='01' and a.sscq = b.cqbh
        //group by b.cqmc";
        //        DataTable dtCq_1 = new DataTable();
        //        Series series_q = new Series("企业类", type);

        //        dtCq = Frame.Global.GlobalSqlHelper.ExecuteSQLToDataTable(strCq);
        //        for (int i = 0; i < dtCq.Rows.Count; i++)
        //        {
        //            SeriesPoint point = new SeriesPoint(dtCq.Rows[i]["cqmc"].ToString(), dtCq.Rows[i]["数量统计"].ToString());

        //            series_q.Points.Add(point);
        //        }

        //        #endregion

        //        #region 社团民非类
        //        string strCq_1 = @"select COUNT(a.dwxz) as 数量统计,b.cqmc from css_dwxx as a,css_cqxx as b 
        // where sscq in (select cqbh from css_cqxx) and dwxz='02' and a.sscq = b.cqbh
        //group by b.cqmc";
        //        Series series_s = new Series("社团民非类", type);
        //        dtCq_1 = Frame.Global.GlobalSqlHelper.ExecuteSQLToDataTable(strCq_1);
        //        for (int i = 0; i < dtCq_1.Rows.Count; i++)
        //        {
        //            SeriesPoint point_1 = new SeriesPoint(dtCq_1.Rows[i]["cqmc"].ToString(), dtCq_1.Rows[i]["数量统计"].ToString());
        //            series_s.Points.Add(point_1);
        //        }

        //        #endregion

        //        #region 公益类
        //        DataTable dtCq_2 = new DataTable();
        //        string strCq_2 = @"select COUNT(a.dwxz) as 数量统计,b.cqmc from css_dwxx as a,css_cqxx as b 
        // where sscq in (select cqbh from css_cqxx) and dwxz='03' and a.sscq = b.cqbh
        //group by b.cqmc";
        //        Series series_g = new Series("公益类", type);
        //        dtCq_2 = Frame.Global.GlobalSqlHelper.ExecuteSQLToDataTable(strCq_2);
        //        for (int i = 0; i < dtCq_2.Rows.Count; i++)
        //        {
        //            SeriesPoint point_2 = new SeriesPoint(dtCq_2.Rows[i]["cqmc"].ToString(), dtCq_2.Rows[i]["数量统计"].ToString());
        //            series_g.Points.Add(point_2);
        //        }

        //        #endregion

        //        #region 社区组织类
        //        DataTable dtCq_3 = new DataTable();
        //        string strCq_3 = @"select COUNT(a.dwxz) as 数量统计,b.cqmc from css_dwxx as a,css_cqxx as b 
        // where sscq in (select cqbh from css_cqxx) and dwxz='04' and a.sscq = b.cqbh
        //group by b.cqmc";
        //        Series series_z = new Series("社区组织类", type);
        //        dtCq_3 = Frame.Global.GlobalSqlHelper.ExecuteSQLToDataTable(strCq_3);
        //        for (int i = 0; i < dtCq_3.Rows.Count; i++)
        //        {
        //            SeriesPoint point_3 = new SeriesPoint(dtCq_3.Rows[i]["cqmc"].ToString(), dtCq_3.Rows[i]["数量统计"].ToString());
        //            series_z.Points.Add(point_3);
        //        }
        //        #endregion
        #endregion
        this.WebChartControl.Series.Clear();
        this.WebChartControl.Series.Add(series_q);
        this.WebChartControl.Series.Add(series_s);
        this.WebChartControl.Series.Add(series_g);
        this.WebChartControl.Series.Add(series_z);
        this.WebChartControl.Legend.Visible = true;

    }
    //饼状图
    void pieDatabind()
    {
        string strSql = @"select cqmc,[01] as qyl ,[02] as stmfl,[03] as gyl,[04] as sqzzl
from(select dwxz,sscq from css_dwxx) as t PIVOT(count(t.dwxz) for t.dwxz in([01],[02],[03],[04])) as p,
css_cqxx as c where c.cqbh = p.sscq";
        DataTable dt = new DataTable();
        dt = Frame.Global.GlobalSqlHelper.ExecuteSQLToDataTable(strSql);
        Series series = new Series("分区", ViewType.Pie);
        this.WebChartControl.Series.Clear();
        this.WebChartControl.Series.Add(series);
        series.ArgumentScaleType = ScaleType.Qualitative;
        series.ValueScaleType = ScaleType.Numerical;

        for (int i = 0; i < dt.Rows.Count; i++)
        {
            string name = dt.Rows[i]["cqmc"].ToString();
            double value = Convert.ToDouble(dt.Rows[i]["qyl"].ToString());
            SeriesPoint point = new SeriesPoint(name, new double[] { value });
            series.Points.Add(point);
        }

        this.WebChartControl.Legend.Visible = true;
    }
    protected void WebChartControl_CustomCallback(object sender, DevExpress.XtraCharts.Web.CustomCallbackEventArgs e)
    {
        string name = cmbSelect.Value.ToString();
        switch (name)
        {
            case "B":
                ChartDataBind(ViewType.Pie);
                gvShow.Visible = false;
                break;
            case "Z":
                ChartDataBind(ViewType.Bar);
                gvShow.Visible = false;
                break;
            default:
                break;
        }
    }

    protected void gvShow_RowCreated(object sender, GridViewRowEventArgs e)
    {
        switch (e.Row.RowType)
        {
            //判断是否表头
            case DataControlRowType.Header:
                //第一行表头
                TableCellCollection tcHeader = e.Row.Cells;
                tcHeader.Clear();

                tcHeader.Add(new TableHeaderCell());
                tcHeader[0].Attributes.Add("rowspan", "2");
                tcHeader[0].Attributes.Add("bgcolor", "Azure");
                tcHeader[0].Text = "编号";

                tcHeader.Add(new TableHeaderCell());
                tcHeader[1].Attributes.Add("rowspan", "2");
                tcHeader[1].Attributes.Add("bgcolor", "Azure");
                tcHeader[1].Text = "社区名称";

                tcHeader.Add(new TableHeaderCell());
                tcHeader[2].Attributes.Add("colspan", "4");
                tcHeader[2].Attributes.Add("bgcolor", "Azure");
                tcHeader[2].Text = "单位性质</th></tr><tr>";

                //第二行表头
                tcHeader.Add(new TableHeaderCell());
                tcHeader[3].Attributes.Add("bgcolor", "Azure");
                tcHeader[3].Text = "企业类";
                tcHeader.Add(new TableHeaderCell());
                tcHeader[4].Attributes.Add("bgcolor", "Azure");
                tcHeader[4].Text = "社团民非类";
                tcHeader.Add(new TableHeaderCell());
                tcHeader[5].Attributes.Add("bgcolor", "Azure");
                tcHeader[5].Text = "公益类";
                tcHeader.Add(new TableHeaderCell());
                tcHeader[6].Attributes.Add("bgcolor", "Azure");
                tcHeader[6].Text = "社区组织类";
                break;
        }
    }
    protected void gvShow_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        //遍历所有行设置边框样式
        foreach (TableCell tc in e.Row.Cells)
        {
            tc.Attributes["style"] = "border-color:Black";
        }
        //用索引来取得编号
        if (e.Row.RowIndex != -1)
        {
            int id = gvShow.PageIndex * gvShow.PageSize + e.Row.RowIndex + 1;
            e.Row.Cells[0].Text = id.ToString();
        }
    }
    //查询
    protected void btnSearch_Click(object sender, EventArgs e)
    {
        string strSql = @"select cqbh,cqmc,[01] as qyl ,[02] as stmfl,[03] as gyl,[04] as sqzzl
        from(select dwxz,sscq from css_dwxx) as t PIVOT(count(t.dwxz) for t.dwxz in([01],[02],[03],[04])) as p,
        css_cqxx as c where {0} c.cqbh = p.sscq";
        StringBuilder sb = new StringBuilder();
        List<SqlParameter> paralist = new List<SqlParameter>();
        if (!string.IsNullOrEmpty(cmbsscq.Text))
        {
            sb.Append("cqbh = @cqbh and ");
            paralist.Add(new SqlParameter("@cqbh", cmbsscq.SelectedItem.Value.ToString()));
        }
        string strsql = string.Format(strSql, sb.ToString());
        this.gvShow.DataSource = Frame.Global.GlobalSqlHelper.ExecuteSQLToDataTable(string.Format(strSql, sb.ToString()), paralist.ToArray());
        this.gvShow.DataBind();
    }
    protected void btnExcel_Click(object sender, EventArgs e)
    {
        Export("application/ms-excel", "单位信息统计报表.xls"); //导出数据  
    }
    private void Export(string FileType, string FileName)
    {
        Response.Clear();
        Response.Charset = "gb2312";
        Response.Buffer = true;
        Response.AddHeader("content-disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, Encoding.UTF8).ToString());
        Response.ContentType = "application/ms-excel";
        Response.Write("<html><head><meta http-equiv=Content-Type content=\"text/html; charset=utf-8\">");
        System.IO.StringWriter sw = new System.IO.StringWriter();
        HtmlTextWriter hw = new HtmlTextWriter(sw);
        this.gvShow.RenderControl(hw);
        Response.Write(sw.ToString());
        Response.Write("</body></html>");
        Response.Flush();
        Response.End();
    }
    /// <summary>   
    /// 必须重写VerifyRenderingInServerForm方法，否则报错   
    /// </summary>   
    /// <param name="control"></param>   
    public override void VerifyRenderingInServerForm(Control control)
    {
    }
    protected void btnClear_Click(object sender, EventArgs e)
    {
        data();
    }

}